Re: [SQL] Anyone recognise this error from PL/pgSQL?

At 17:57 +0300 on 16/08/1999, Stuart Rison wrote:


> CREATE FUNCTION test(int2) RETURNS int2 AS '
> SELECT field2 FROM test
>     WHERE field1=$1;
> ' language 'sql';
>
> ERROR:  There is no operator '=$' for types 'int2' and 'int4'
>         You will either have to retype this query using an explicit cast,
>         or you will have to define the operator using CREATE OPERATOR
>
> Same problem with SQL functions (this is with PG6.4).
>
> I don't know what the standard syntax for operators is but -if it is not
> compulsary to "bound" the operator with spaces- I guess it's a (minor) bug
> with the SQL parser.

It's a lexical analysis problem, not a parsing problem. When you see the
string 'WHERE abcdefg=$1', do you tokenize it as
<WHERE> <abcdefg> <=$> <1>
or as
<WHERE> <abcdefg> <=> <$1>

Now, imagine someone defining the boolean operator  n =$ k
As meaning "n has k digits". ( 10 =$ 2 is true, 1 =$ 2 is false).

Either one of the two interpretations of the WHERE clause would then be a
valid one!

How does one avoid a conflict? You can disallow such operators (any
operator with a right-side $) - but that would limit the users and may
potentially hurt existing programs. You can require that spaces are always
around operators, disallowing things like field1=3 - but that would hurt
even more existing pragrams. Or, you can default to one of the two
interpretations. The one that expects =$ seems to be the more obvious
default.

Bottom line is, however, that this is a rather problematic error message.
It's not much in the way of showing the user what he did wrong. Perhaps
adding a line saying that "this may result from not having a space between
an operator and a variable" would do the trick. And the message is
completely out of touch when the $ results from an internal string
replcement, in which case I really think it is recommended to add the
spaces when replacing the string. It can't harm, and it will avoid the
problems for sure.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma